/*==============================================================*/
/* DBMS name:      Microsoft SQL Server 2012                    */
/* Created on:     2021/8/16 11:21:09                           */
/*==============================================================*/


if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('cart_info') and o.name = 'FK_CART_INF_REFERENCE_COMMODIT')
alter table cart_info
   drop constraint FK_CART_INF_REFERENCE_COMMODIT
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('cart_info') and o.name = 'FK_CART_INF_REFERENCE_CUSTOMER')
alter table cart_info
   drop constraint FK_CART_INF_REFERENCE_CUSTOMER
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('commodity_SKU') and o.name = 'FK_COMMODIT_REFERENCE_COMMODIT')
alter table commodity_SKU
   drop constraint FK_COMMODIT_REFERENCE_COMMODIT
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('commodity_SKU') and o.name = 'FK_COMMODIT_REFERENCE_COMMODIT')
alter table commodity_SKU
   drop constraint FK_COMMODIT_REFERENCE_COMMODIT
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('commodity_flie') and o.name = 'FK_COMMODIT_REFERENCE_COMMODIT')
alter table commodity_flie
   drop constraint FK_COMMODIT_REFERENCE_COMMODIT
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('commodity_message_info') and o.name = 'FK_COMMODIT_REFERENCE_MERCHANT')
alter table commodity_message_info
   drop constraint FK_COMMODIT_REFERENCE_MERCHANT
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('commodity_message_info') and o.name = 'FK_COMMODIT_REFERENCE_PRODUCT_')
alter table commodity_message_info
   drop constraint FK_COMMODIT_REFERENCE_PRODUCT_
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('commodity_message_info') and o.name = 'FK_COMMODIT_REFERENCE_BRAND_IN')
alter table commodity_message_info
   drop constraint FK_COMMODIT_REFERENCE_BRAND_IN
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('commodity_specification') and o.name = 'FK_COMMODIT_REFERENCE_SPECIFIC')
alter table commodity_specification
   drop constraint FK_COMMODIT_REFERENCE_SPECIFIC
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('commodity_specification') and o.name = 'FK_COMMODIT_REFERENCE_SPECIFIC')
alter table commodity_specification
   drop constraint FK_COMMODIT_REFERENCE_SPECIFIC
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('customer_addr') and o.name = 'FK_CUSTOMER_REFERENCE_CUSTOMER')
alter table customer_addr
   drop constraint FK_CUSTOMER_REFERENCE_CUSTOMER
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('customer_order') and o.name = 'FK_CUSTOMER_REFERENCE_CUSTOMER')
alter table customer_order
   drop constraint FK_CUSTOMER_REFERENCE_CUSTOMER
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('product_category') and o.name = 'FK_PRODUCT__REFERENCE_PRODUCT_')
alter table product_category
   drop constraint FK_PRODUCT__REFERENCE_PRODUCT_
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('specification_value') and o.name = 'FK_SPECIFIC_REFERENCE_SPECIFIC')
alter table specification_value
   drop constraint FK_SPECIFIC_REFERENCE_SPECIFIC
go

if exists (select 1
            from  sysobjects
           where  id = object_id('admin')
            and   type = 'U')
   drop table admin
go

if exists (select 1
            from  sysobjects
           where  id = object_id('brand_info')
            and   type = 'U')
   drop table brand_info
go

if exists (select 1
            from  sysobjects
           where  id = object_id('cart_info')
            and   type = 'U')
   drop table cart_info
go

if exists (select 1
            from  sysobjects
           where  id = object_id('commodity_SKU')
            and   type = 'U')
   drop table commodity_SKU
go

if exists (select 1
            from  sysobjects
           where  id = object_id('commodity_flie')
            and   type = 'U')
   drop table commodity_flie
go

if exists (select 1
            from  sysobjects
           where  id = object_id('commodity_message_info')
            and   type = 'U')
   drop table commodity_message_info
go

if exists (select 1
            from  sysobjects
           where  id = object_id('commodity_specification')
            and   type = 'U')
   drop table commodity_specification
go

if exists (select 1
            from  sysobjects
           where  id = object_id('customer')
            and   type = 'U')
   drop table customer
go

if exists (select 1
            from  sysobjects
           where  id = object_id('customer_addr')
            and   type = 'U')
   drop table customer_addr
go

if exists (select 1
            from  sysobjects
           where  id = object_id('customer_order')
            and   type = 'U')
   drop table customer_order
go

if exists (select 1
            from  sysobjects
           where  id = object_id('merchant_info')
            and   type = 'U')
   drop table merchant_info
go

if exists (select 1
            from  sysobjects
           where  id = object_id('product_category')
            and   type = 'U')
   drop table product_category
go

if exists (select 1
            from  sysobjects
           where  id = object_id('specification_info')
            and   type = 'U')
   drop table specification_info
go

if exists (select 1
            from  sysobjects
           where  id = object_id('specification_value')
            and   type = 'U')
   drop table specification_value
go

/*==============================================================*/
/* Table: admin                                                 */
/*==============================================================*/
create table admin (
   id                   int                  not null,
   admin_name           nvarchar(50)         null,
   admin_password       nvarchar(50)         null,
   admin_avatar         nvarchar(300)        null,
   IsActived            bit                  null default 1,
   IsDeleted            bit                  null default 0,
   DateTime             datetime             null,
   updatetime           datetime             null,
   constraint PK_ADMIN primary key (id)
)
go

/*==============================================================*/
/* Table: brand_info                                            */
/*==============================================================*/
create table brand_info (
   id                   int                  identity,
   brand_name           nvarchar(40)         null,
   brand_logo           nvarchar(400)        null,
   IsActived            bit                  null default 1,
   IsDeleted            bit                  null default 0,
   DateTime             datetime             null,
   updatetime           datetime             null,
   constraint PK_BRAND_INFO primary key (id)
)
go

/*==============================================================*/
/* Table: cart_info                                             */
/*==============================================================*/
create table cart_info (
   id                   int                  not null,
   customer_id          int                  null,
   price                decimal(10,2)        null,
   number               int                  null,
   cart_url             nvarchar(500)        null,
   IsActived            bit                  null default 1,
   IsDeleted            bit                  null default 0,
   DateTime             datetime             null,
   updatetime           datetime             null,
   commodity_SKU_id     int                  null,
   constraint PK_CART_INFO primary key (id)
)
go

/*==============================================================*/
/* Table: commodity_SKU                                         */
/*==============================================================*/
create table commodity_SKU (
   id                   int                  not null,
   commodity_inventory  int                  null,
   commodity_money      decimal(10,2)        null,
   commodity_id         int                  null,
   commodity_specification_id int                  null,
   constraint PK_COMMODITY_SKU primary key (id)
)
go

/*==============================================================*/
/* Table: commodity_flie                                        */
/*==============================================================*/
create table commodity_flie (
   id                   int                  not null,
   commodity_id         int                  null,
   commodity_name       nvarchar(400)        null,
   constraint PK_COMMODITY_FLIE primary key (id)
)
go

/*==============================================================*/
/* Table: commodity_message_info                                */
/*==============================================================*/
create table commodity_message_info (
   id                   int                  not null,
   commodity_message_name nvarchar(80)         null,
   product_id           int                  null,
   commodity_message_brief nvarchar(300)        null,
   commodity_message_file nvarchar(300)        null,
   commodity_message_detail nvarchar(max)        null,
   IsDeleted            bit                  null default 0,
   DateTime             datetime             null,
   updatetime           datetime             null,
   brand_id             int                  null,
   merchant_id          int                  null,
   constraint PK_COMMODITY_MESSAGE_INFO primary key (id)
)
go

/*==============================================================*/
/* Table: commodity_specification                               */
/*==============================================================*/
create table commodity_specification (
   id                   int                  not null,
   specification_id     int                  null,
   specification_value_id int                  null,
   constraint PK_COMMODITY_SPECIFICATION primary key (id)
)
go

/*==============================================================*/
/* Table: customer                                              */
/*==============================================================*/
create table customer (
   Id                   int                  identity,
   customer_login_name  nvarchar(50)         null,
   customer_login_oassword nvarchar(50)         null,
   IsActived            bit                  null,
   IsDeleted            bit                  null,
   phone                nvarchar(20)         null,
   customer_card        nvarchar(20)         null,
   DateTime             datetime             null,
   is_merchant          bit                  null,
   constraint PK_CUSTOMER primary key (Id)
)
go

/*==============================================================*/
/* Table: customer_addr                                         */
/*==============================================================*/
create table customer_addr (
   id                   int                  identity,
   customer_id          int                  null,
   customer_address     nvarchar(400)        null,
   IsActived            bit                  null,
   IsDeleted            bit                  null,
   DateTime             datetime             null,
   updatetime           datetime             null,
   customer_name        nvarchar(50)         null,
   customer_phone       nvarchar(30)         null,
   constraint PK_CUSTOMER_ADDR primary key (id)
)
go

/*==============================================================*/
/* Table: customer_order                                        */
/*==============================================================*/
create table customer_order (
   id                   int                  identity,
   customer_id          int                  null,
   commodity            int                  null,
   state                bit                  null default 1,
   IsActived            bit                  null,
   IsDeleted            bit                  null,
   DateTime             datetime             null,
   updatetime           datetime             null,
   constraint PK_CUSTOMER_ORDER primary key (id)
)
go

/*==============================================================*/
/* Table: merchant_info                                         */
/*==============================================================*/
create table merchant_info (
   id                   int                  identity,
   merchart_phone       nvarchar(20)         null,
   bank_accout          nvarchar(50)         null,
   address              nvarchar(300)        null,
   IsActived            bit                  null,
   IsDeleted            bit                  null,
   DateTime             datetime             null,
   updatetime           datetime             null,
   merchant_username    nvarchar(50)         null,
   merchant_password    nvarchar(50)         null,
   merchant_user        nvarchar(20)         null,
   constraint PK_MERCHANT_INFO primary key (id)
)
go

/*==============================================================*/
/* Table: product_category                                      */
/*==============================================================*/
create table product_category (
   id                   int                  not null,
   category_name        nvarchar(20)         null,
   parent_id            int                  null,
   category_level       bit                  null,
   IsActived            bit                  null default 1,
   IsDeleted            bit                  null default 0,
   DateTime             datetime             null,
   updatetime           datetime             null,
   constraint PK_PRODUCT_CATEGORY primary key (id)
)
go

/*==============================================================*/
/* Table: specification_info                                    */
/*==============================================================*/
create table specification_info (
   id                   int                  not null,
   specification_name   nvarchar(20)         null,
   constraint PK_SPECIFICATION_INFO primary key (id)
)
go

/*==============================================================*/
/* Table: specification_value                                   */
/*==============================================================*/
create table specification_value (
   id                   int                  not null,
   specification_id     int                  null,
   specification_value_id nvarchar(255)        null,
   constraint PK_SPECIFICATION_VALUE primary key (id)
)
go

alter table cart_info
   add constraint FK_CART_INF_REFERENCE_COMMODIT foreign key (commodity_SKU_id)
      references commodity_SKU (id)
go

alter table cart_info
   add constraint FK_CART_INF_REFERENCE_CUSTOMER foreign key (customer_id)
      references customer (Id)
go

alter table commodity_SKU
   add constraint FK_COMMODIT_REFERENCE_COMMODIT foreign key (commodity_id)
      references commodity_message_info (id)
go

alter table commodity_SKU
   add constraint FK_COMMODIT_REFERENCE_COMMODIT1 foreign key (commodity_specification_id)
      references commodity_specification (id)
go

alter table commodity_flie
   add constraint FK_COMMODIT_REFERENCE_COMMODIT2 foreign key (commodity_id)
      references commodity_message_info (id)
go

alter table commodity_message_info
   add constraint FK_COMMODIT_REFERENCE_MERCHANT3 foreign key (merchant_id)
      references merchant_info (id)
go

alter table commodity_message_info
   add constraint FK_COMMODIT_REFERENCE_PRODUCT_ foreign key (product_id)
      references product_category (id)
go

alter table commodity_message_info
   add constraint FK_COMMODIT_REFERENCE_BRAND_IN foreign key (brand_id)
      references brand_info (id)
go

alter table commodity_specification
   add constraint FK_COMMODIT_REFERENCE_SPECIFIC foreign key (specification_value_id)
      references specification_value (id)
go

alter table commodity_specification
   add constraint FK_COMMODIT_REFERENCE_SPECIFIC4 foreign key (specification_id)
      references specification_info (id)
go

alter table customer_addr
   add constraint FK_CUSTOMER_REFERENCE_CUSTOMER foreign key (customer_id)
      references customer (Id)
go

alter table customer_order
   add constraint FK_CUSTOMER_REFERENCE_CUSTOMER5 foreign key (customer_id)
      references customer (Id)
go

alter table product_category
   add constraint FK_PRODUCT__REFERENCE_PRODUCT_ foreign key (parent_id)
      references product_category (id)
go

alter table specification_value
   add constraint FK_SPECIFIC_REFERENCE_SPECIFIC foreign key (specification_id)
      references specification_info (id)
go

